package com.liufei.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * @author LiuFei
 * @create 2017-12-25 15:18
 */
public class PoiUtils {



    /**
     * 将workbook对象写入xls文件
     *
     * @param filePath 文件路径
     * @param workbook 工作簿
     * @throws IOException
     */
    public static void write(String filePath, HSSFWorkbook workbook) throws IOException {

        FileOutputStream fileOutputStream = new FileOutputStream(filePath);
        workbook.write(fileOutputStream);
        fileOutputStream.flush();
        fileOutputStream.close();
    }

    /**
     * 为一览表设置标题
     *
     * @param workbook 工作簿对象
     * @param sheet 表对象
     */
    public static void setProfileTitle(HSSFWorkbook workbook, HSSFSheet sheet) {
        HSSFCell cell = sheet.createRow(0).createCell(0);
        cell.setCellValue("数据表一览");
        CellRangeAddress cra = new CellRangeAddress(0, 1, 0, 5);
        sheet.addMergedRegion(cra);

        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        HSSFFont font = workbook.createFont();
        font.setFontHeightInPoints(((short) 18));
        font.setFontName("微软雅黑");
        style.setFont(font);
        cell.setCellStyle(style);
    }

    /**
     * 为详情表设置标题
     *
     * @param workbook 工作簿对象
     * @param sheet 表对象
     * @param map 表名信息
     */
    public static void setDetailTitle(HSSFWorkbook workbook, HSSFSheet sheet, Map<String, String> map) {
        HSSFRow row0 = sheet.createRow(0);
        HSSFRow row1 = sheet.createRow(1);
        HSSFCell cell00 = row0.createCell(0);
        HSSFCell cell10 = row1.createCell(0);
        HSSFCell cell01 = row0.createCell(1);
        HSSFCell cell11 = row1.createCell(1);
        cell00.setCellValue("表中文名");
        cell10.setCellValue("表英文名");
        cell01.setCellValue(map.get("REMARKS"));
        cell11.setCellValue(map.get("TABLE_NAME"));

        HSSFCellStyle headStyle = workbook.createCellStyle();
        HSSFFont headFont = workbook.createFont();
        headFont.setFontName("微软雅黑");
        headFont.setFontHeightInPoints(((short) 14));
        headFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        headStyle.setFont(headFont);
        headStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        headStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        headStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        headStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        headStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
        headStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cell00.setCellStyle(headStyle);
        cell10.setCellStyle(headStyle);

        HSSFCellStyle contextStyle = workbook.createCellStyle();
        HSSFFont contextFont = workbook.createFont();
        contextFont.setFontName("微软雅黑");
        contextFont.setFontHeightInPoints(((short) 14));
        contextStyle.setFont(contextFont);
        contextStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        contextStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        contextStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        contextStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        contextStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);

        cell01.setCellStyle(contextStyle);
        cell11.setCellStyle(contextStyle);

        CellRangeAddress cra0 = new CellRangeAddress(0, 0, 1, 6);
        sheet.addMergedRegion(cra0);
        CellRangeAddress cra1 = new CellRangeAddress(1, 1, 1, 6);
        sheet.addMergedRegion(cra1);

        row0.createCell(2).setCellStyle(contextStyle);
        row0.createCell(3).setCellStyle(contextStyle);
        row0.createCell(4).setCellStyle(contextStyle);
        row0.createCell(5).setCellStyle(contextStyle);
        row0.createCell(6).setCellStyle(contextStyle);

        row1.createCell(2).setCellStyle(contextStyle);
        row1.createCell(3).setCellStyle(contextStyle);
        row1.createCell(4).setCellStyle(contextStyle);
        row1.createCell(5).setCellStyle(contextStyle);
        row1.createCell(6).setCellStyle(contextStyle);
    }

    /**
     * 设置表头
     *
     * @param sheet 表对象
     * @param num 表头所在行
     * @param headContext 表头内容
     * @param headCellStyle 表头样式
     */
    public static void setHeadRow(HSSFSheet sheet, int num, String[] headContext, HSSFCellStyle headCellStyle) {
        HSSFRow headRow = sheet.createRow(num);
        int currCol = 0;
        for (String str : headContext) {
            HSSFCell cell = headRow.createCell(currCol++);
            cell.setCellValue(str);
            cell.setCellStyle(headCellStyle);
        }
    }
    /**
     * 一览表中的内容
     *
     * @param sheet 表对象
     * @param num 表中的第几行插入
     * @param count 序号
     * @param map 行数据
     * @param contextCellStyle 样式
     * @return 新增的行
     */
    public static HSSFRow setProfileContextRow(HSSFSheet sheet, int num, int count, Map<String, String> map, HSSFCellStyle contextCellStyle) {
        HSSFRow row = sheet.createRow(num);
        HSSFCell cell0 = row.createCell(0);
        cell0.setCellValue(count);
        cell0.setCellStyle(contextCellStyle);

        HSSFCell cell1 = row.createCell(1);
        cell1.setCellValue(map.get("REMARKS"));
        cell1.setCellStyle(contextCellStyle);
        // 添加超链接
        HSSFHyperlink hyperlink = new HSSFHyperlink(Hyperlink.LINK_DOCUMENT);
        hyperlink.setAddress("#" + map.get("REMARKS") + "!B1");
        cell1.setHyperlink(hyperlink);
        // 设置超链接样式
        HSSFCellStyle hyperStyle = sheet.getWorkbook().createCellStyle();
        HSSFFont hyperFont = sheet.getWorkbook().createFont();
        hyperFont.setUnderline((byte) 1);
        hyperFont.setColor(HSSFColor.BLUE.index);
        hyperFont.setFontName("微软雅黑");
        hyperFont.setFontHeightInPoints(((short) 12));
        hyperStyle.setFont(hyperFont);
        hyperStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        hyperStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        hyperStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        hyperStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cell1.setCellStyle(hyperStyle);

        HSSFCell cell2 = row.createCell(2);
        cell2.setCellValue(map.get("TABLE_NAME"));
        cell2.setCellStyle(contextCellStyle);
        HSSFCell cell3 = row.createCell(3);
        cell3.setCellStyle(contextCellStyle);
        HSSFCell cell4 = row.createCell(4);
        cell4.setCellStyle(contextCellStyle);
        HSSFCell cell5 = row.createCell(5);
        cell5.setCellStyle(contextCellStyle);

        return row;
    }

    /**
     * 设置一览表的格式
     *
     * @param sheet 表对象
     */
    public static void setProfileWidth(HSSFSheet sheet) {
        sheet.setColumnWidth(0, 15 * 256);
        sheet.setColumnWidth(1, 25 * 256);
        sheet.setColumnWidth(2, 30 * 256);
        sheet.setColumnWidth(3, 40 * 256);
        sheet.setColumnWidth(4, 20 * 256);
        sheet.setColumnWidth(5, 30 * 256);
    }

    /**
     * 设置每个存储表结构信息的表格式
     *
     * @param colSheet 表对象
     */
    public static void setDetailWidth(HSSFSheet colSheet) {
        colSheet.setColumnWidth(0, 25 * 256);
        colSheet.setColumnWidth(1, 25 * 256);
        colSheet.setColumnWidth(2, 15 * 256);
        colSheet.setColumnWidth(3, 15 * 256);
        colSheet.setColumnWidth(4, 15 * 256);
        colSheet.setColumnWidth(5, 15 * 256);
        colSheet.setColumnWidth(6, 35 * 256);
    }

    /**
     * 每个存储表结构信息的表填充内容
     *
     * @param colSheet 表对象
     * @param num 从第几行设置内容
     * @param columnList 内容
     * @param contextCellStyle 样式
     */
    public static void setDetailContext(HSSFSheet colSheet, int num, List<Map<String, String>> columnList, HSSFCellStyle contextCellStyle) {
        for (Map<String, String> colMap : columnList) {
            HSSFRow row = colSheet.createRow(num++);
            Set<Map.Entry<String, String>> entries = colMap.entrySet();
            int currCol = 0;
            String remark;
            String[] remarks = null;
            for (Map.Entry<String, String> entry : entries) {
                if (currCol == 0) {
                    // 对第一列进行特殊处理
                    remark = entry.getValue();
                    HSSFCell cell = row.createCell(currCol);
                    if (remark != null && remark.contains(":")) {
                        remarks = RemarkUtil.splitRemark(remark);
                        cell.setCellValue(remarks[0]);
                        cell.setCellStyle(contextCellStyle);
                    } else {
                        cell.setCellValue(remark);
                        cell.setCellStyle(contextCellStyle);
                    }
                } else {
                    HSSFCell cell = row.createCell(currCol);
                    cell.setCellValue(entry.getValue());
                    cell.setCellStyle(contextCellStyle);
                }
                if (remarks != null) {
                    HSSFCell cell6 = row.createCell(6);
                    cell6.setCellValue(remarks[1]);
                    cell6.setCellStyle(contextCellStyle);
                } else {
                    HSSFCell cell6 = row.createCell(6);
                    cell6.setCellStyle(contextCellStyle);
                }
                currCol++;
            }
        }
    }

    /**
     * 返回内容部分的格式
     *
     * @param workbook 工作簿对象
     * @return 样式
     */
    public static HSSFCellStyle getContextStyle(HSSFWorkbook workbook) {
        HSSFCellStyle contextCellStyle = workbook.createCellStyle();
        // 字体
        HSSFFont font = workbook.createFont();
        font.setFontName("微软雅黑");
        font.setFontHeightInPoints(((short) 12));
        contextCellStyle.setFont(font);

        // 边框
        contextCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        contextCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        contextCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        contextCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

        return contextCellStyle;
    }

    /**
     * 标题的样式
     *
     * @param workbook 工作簿对象
     * @return 样式
     */
    public static HSSFCellStyle getHeadStyle(HSSFWorkbook workbook) {
        HSSFCellStyle headCellStyle = workbook.createCellStyle();
        // 字体
        HSSFFont font = workbook.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontName("微软雅黑");
        font.setFontHeightInPoints(((short) 12));
        headCellStyle.setFont(font);

        // 边框
        headCellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        headCellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        headCellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        headCellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);

        headCellStyle.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
        headCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

        // 居中
        headCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        return headCellStyle;
    }
}
